SheetJS で Excel のシリアル値を読み込む際に「日付システム」が異なるとどうなるのかを確認してみた
こんにちは！DA(データアナリティクス)事業本部 サービスソリューション部の大高です。
Excelでは、日時のデータは「シリアル値」と呼ばれる、数値として格納されています。
この「シリアル値」をSheetJSというExcelデータを扱えるJavaScriptライブラリで扱う機会があったので、情報をまとめてみたいと思います。
「シリアル値」と「日付システム」
まずはじめに「シリアル値」と、その「シリアル値」に密接に関わる「日付システム」についてです。
Excelでは「1900 日付システム」と「1904 日付システム」という2つの日付システムがサポートされています。
基本的にはどちらも同じ考え方で「シリアル値」の「開始日付をいつにするか」という違いになります。例えば「1900 日付システム」においては、「1900年1月1日」からの経過日数が「シリアル値」となります。
こちらについては、下記のドキュメントが参考になります。
1900 日付システム
こちらはドキュメントに記述されているとおり、「1900年1月1日」を開始日とする日付システムです。
1900 年の日付システムでは、開始日として 1900 年 1 月 1 日を使用して日付が計算されます。日付を入力すると、1900 年 1 月 1 日からの経過日数を表すシリアル値に変換されます。
これは、以下のExcelにおけるデフォルトの日付システムとなっています。また、私が現在 MacOS で利用している Microsoft 365 における Excel for Mac(16.64) においても同様でした。
- Excel for Windows
- Excel 2016 for Mac
- Excel for Mac 2011
1904 日付システム
こちらは「1904年1月1日」を開始日とする日付システムです。
1904 年の日付システムでは、開始日として 1904 年 1 月 1 日を使用して日付が計算されます。日付を入力すると、1904 年 1 月 1 日からの経過日数を表すシリアル値に変換されます。
これは、以前のバージョンの Excel for Mac におけるデフォルトの日付システムだったそうです。
2つの日付システムの背景
上記のとおり「日付システム」には2つの「日付システム」が存在しており、オプション設定で切り替えることができます。
では、そもそもなぜ2つも「日付システム」が存在しているのでしょうか。
こちらについては、以下のドキュメントに記載されています。
要点として簡単にまとめると以下のようになります。
- 初期のExcel for Macでは「1904 日付システム」が採用された
- 「1904 日付システム」だと「1900年がうるう年ではない」とう考慮をしなくて良い
- 「1900 日付システム」では、「Lotus 1-2-3」と同じように「1900年をうるう年である」として扱った
「1900 日付システム」では、あえて 1900/2/29 が存在するようになっているのですね。初めて知りました！
SheetJSと「シリアル値」について
SheetJSでは、Excel上の値を「そのままの値として取得する」か「日付として取得する」かをオプションで指定することができます。
今回はこのオプションを利用して、取得したオブジェクトがどのようになるかを確認したいと思います。
検証環境
検証環境は以下の通りです。
- Microsoft Excel for Mac
- バージョン 16.64 (22081401)
- SheetJS
- 0.18.8
なお、詳細は省きますが SheetJS は Next.js の中で利用しています。
検証方法
検証方法としては、下記の2パターンでExcelファイルを読み込み、データのチェックを行うことにしました。
- シリアル値として読み込んだ場合
const workbook: WorkBook = XLSX.read(fileData, {}) console.log('Workbook:', workbook) -
日付値として読み込んだ場合
const workbook: WorkBook = XLSX.read(fileData, { cellDates: true }) console.log('Workbook:', workbook)
詳細は省きますが、オプションで cellDates を指定することによって、読み込みする値を日付値として読み込むようにしています。
検証で読み込むExcelファイルは、以下のテーブル内容がA1からB2にかけて記述されているシートを1つ持つファイルです。ファイルは2つ用意し、「1900 日付システム」と「1904 日付システム」の2つのファイルを用意しました。
| 日付 | 備考 |
|---|---|
| 1905/01/01 | 1905年1月1日の値です。 |
検証結果
検証結果は以下のようになりました。
「1900 日付システム」のExcelファイル場合
- ワークブックのプロパティ
workbook.Workbook.WBProps.date1904の値false
- シリアル値
1828
- 日付値
Sun Jan 01 1905 00:00:00 GMT+0900 (日本標準時)
「1904 日付システム」のExcelファイル場合
- ワークブックのプロパティ
workbook.Workbook.WBProps.date1904の値true
- シリアル値
366
- 日付値
Mon Dec 31 1900 00:00:00 GMT+0900 (日本標準時)
考察
上記の結果から、「シリアル値」については公表のとおり 1462 の差分値があることが確認できました。（想定どおり）
一方で「日付値」については、2022年8月現在では想定外の値となっているようです。なお、追加検証として SheetJS のバージョンを 0.18.10 でも試してみましたが、結果は同様でした。
現状では、日付の値は「シリアル値」のままで持っておくか、プロパティ値 workbook.Workbook.WBProps.date1904 を元に判断して、「日付値」を変換するしかないかもしれません。
なお、こちらの件については以下のIssueでも討議されているようです。
私は対応方法として、日付の値を「シリアル値」のまま持つ、という方法で対応をしました。
おまけ
その他に、調査の過程で得られた情報は以下になります。
時間をもつ（日時の）場合、シリアル値はどうなるのか
時間をもつ（日時の）場合、シリアル値はどうなるのかを確認してみましたが、これは小数点以下の値を持っていました。
たとえば 1905/1/1 12:00:00 の場合には、シリアル値は 1828.5 となっていました。小数点の精度が気になるところではありますが、面白いですね。
SheetJSにおいて「日付」をどのように判断しているか
SheetJSではセルの値は、セルの「フォーマット」を利用して判断しているようです。
以下が該当の判断箇所と思われます。
実際の処理である fmt_is_date はこちらですね。
なお、fmt_is_date は SSF.is_date として呼び出すことができそうです。
SheetJSでの「日付値」ではタイムゾーンをどう判断しているか
これは下記のIssueにおいて言及されていますが、JSエンジンが動く環境（ブラウザなど）に依存しているそうです。
The internal representation of date cells is the date as understood in the JS engine timezone.
また、「Excelはタイムゾーンを保持していない」ということにも言及されていました。
"timezone": The previous point wouldn't normally be a huge issue, but Excel doesn't store any information about the timezone of the computer that saved the file. There's no way to know the universal date corresponding to the value. XLS has a "Country" record, but that's useless for countries like USA which have multiple timezones.
ここは「シリアル値」から「日付値」に変換する際の注意ポイントですね。
なお、変換には以下の numdate 関数がうまく利用できそうな感じでした。
まとめ
以上、SheetJS で Excel のシリアル値を読み込む際に「日付システム」が異なるとどうなるのかを確認してみました。
Excelの内部では日付を数値で扱っている、ということは ぼんやりとは知っていましたが、今回調べてみて新しい発見が多くて面白かったです！
どなたかのお役に立てば幸いです。それでは！
![[新機能]Microsoft Excelからdbt Semantic Layerに接続するためのアドオンがリリースされました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
